%macro sample_select ; 

/* this macro creates CTE flag  */
%macro cte_flag ; 

degcip_2dig in ('10','11','12','13','15','19',
                '43','44','46','47','48','49','51','52') 


%mend ;


data graduates (keep= pik degcip_2dig opeid year_grad quarter_grad qtime_grad
					deglevl_code state flag_cte) /view=graduates;

    merge INPUTS.pseo_oh_odhe_2002_2019b_deg_rsch (in=a drop=year_grad) 
          INPUTS.pseo_oh_odhe_2002_2019b_deg_pik (in=b) ;
    by dqb_source_id ; 
    
    state = '39' ;
    degcip_2dig = substr(degcip,1,2) ;
    flag_cte = %cte_flag;
    
    date_grad_num = input(date_grad,yymmdd10.) ;
    
    quarter_grad = qtr(date_grad_num) ;
    year_grad = year(date_grad_num) ;
    qtime_grad = %qtime(year_grad,quarter_grad,location=datastep) ;
    
    if deglevl_code in ('01','02','03','04') ;
    
    if year_grad>=2001 ; 
run;

proc contents data=graduates ;
	title 'graduates contents' ;
run;

proc freq data=INPUTS.pseo_oh_odhe_2002_2019a_enr_rsch ; 
	tables Term student_level ; 
	title 'Terms' ;
run;

proc sort data=INPUTS.pseo_oh_odhe_2002_2019a_enr_qpik out=qpik ;
	by dqb_source_id ; 
run;

proc sort data=INPUTS.pseo_oh_odhe_2002_2019a_enr_rsch out=rsch ;
	by dqb_source_id ; 
run;

proc contents data=rsch ; 
	title 'contents of research dataset' ;
run;

proc print data=rsch (obs=20) ;
	title 'observations and seeing credit hours' ;
run;

data nongraduates (keep = pik degcip_2dig opeid  credithours year 
						quarter state cte_flag deglevl_code )
    /view=nongraduates;
    
    merge rsch (in=a rename=(opeid=opeid_num calendar_year=year 
							credit_hours = credithours)) 
          qpik (in=b rename=(qpik_ssn = pik )) ;
    by dqb_source_id ; 
    
    state = '39' ; 
	opeid_char = put(opeid_num,8.) ;
	opeid = "00" || substr(opeid_char,1,6) ;

	if student_level = "To obtain a certificate" then deglevl_code = '01' ;
	if student_level = "To obtain an associate degree for the job market" then deglevl_code='03' ;
	if student_level = "To obtain an associate degree for transfer" then deglevl_code ='03' ;

    /* need to add qtimes */
		
	if term = "AU" then quarter = 4 ;
	if term = "WI" then quarter = 1 ; 
	if term = "SP" then quarter = 2 ; 
	if term = "SM" then quarter = 3 ; 
	degcip_2dig = substr(major,1,2) ;
    cte_flag=%cte_flag;
    
    if year>=2001 & deglevl_code in ('01','02','03','04') ;
run;


     /* need to get max credit hours */ 
proc sort data=nongraduates out=nongrads; 
	by pik opeid deglevl_code year quarter ; 
run;

proc contents data=nongrads ; 

proc print data=nongrads (obs=10) ;
	var pik opeid deglevl_code year quarter credithours ; 
run;

/* generating cumulative credit hours */
data nongrads ; 
	set nongrads ; 
	by pik opeid deglevl_code year quarter ;
	if first.deglevl_code then do; 
		cumulative_hrs = 0 ; 
	end;
	
	cumulative_hrs = cumulative_hrs + credithours ;
	
run;

proc print data=nongrads (obs=10) ;
	var pik opeid deglevl_code year quarter credithours cumulative_hrs; 
run;
    
proc summary data=nongrads nway ;
    class pik opeid deglevl_code ;
    var credithours cte_flag;
    output out=maxcrd (drop = _TYPE_ _FREQ_) 
            sum(credithours) = maxcrd 
            max( cte_flag)= cte_flag;
run;

proc freq data=nongrads ; 
	tables cte_flag ;
	title 'cte flag in nongrads' ;
run;
    
proc sort data=nongrads ;
    by pik opeid deglevl_code ; 
run;
    
proc print data=maxcrd (obs=50) ; 
	title 'maxcrd observations' ;
run;

data nongraduates_finalenroll (drop=credithours); 
    merge nongrads (in=a drop=cte_flag) maxcrd (in=b) ;
    by pik opeid deglevl_code;
    
    if maxcrd >=10 ;
            
    if last.deglevl_code and cte_flag = 1 then output; 
run;
            
proc sort data=graduates out=grads nodupkey ;
     by pik opeid deglevl_code ; 
run;
            
proc freq data=grads ;
      title 'CTE FLAG GRADS ' ;
      tables flag_cte ; 
run;

data OUTPUTS.all_students_odhe (drop=maxcrd cte_flag); 
       merge grads (in=a) 
             nongraduates_finalenroll ; 
       by pik opeid deglevl_code ; 
            
       graduate = a ; 
            
       if (flag_cte and graduate) or not graduate ;
run;

%mend sample_select;  
%sample_select;
